﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using QuanLyThuVien.DTO;
using System.Data.Linq;

namespace QuanLyThuVien.DAO
{
    class DocGiaDAO
    {

        public static DataTable DSDG()
        {
           
            NorthwindDataContext db = new NorthwindDataContext();
            var ds = from dg in db.DocGias
                     join loaiDG in db.LoaiDocGias on dg.MaLoaiDocGia equals loaiDG.MaLoaiDocGia
                     join nv in db.NhanViens on dg.MaNV equals nv.MaNhanVien
                     select new
                     {
                         dg.MaDocGia,
                         dg.HoTenDocGia,
                         dg.NgaySinh,
                         dg.DiaChi,
                         dg.Email,
                         dg.NgayLapThe,
                         dg.NgayHetHan,
                         loaiDG.TenLoaiDocGia,
                         nv.HoTenNhanVien,
                         dg.MaLoaiDocGia,

                     };
            DataTable dt = new DataTable();
            dt.Columns.Add("Mã Độc Giả", typeof(int));
            dt.Columns.Add("Họ Tên Độc Giả", typeof(string));
            dt.Columns.Add("Ngày Sinh", typeof(DateTime));
            dt.Columns.Add("Địa Chỉ", typeof(string));
            dt.Columns.Add("Email", typeof(string));
            dt.Columns.Add("Ngày Lập Thẻ", typeof(DateTime));
            dt.Columns.Add("Loại Độc Giả", typeof(string));
            dt.Columns.Add("Ngày Hết Hạn", typeof(DateTime));
            dt.Columns.Add("Người Lập Thẻ", typeof(string));
            dt.Columns.Add("Ma Loai Doc Gia", typeof(int));

            foreach (var gtri in ds)
            {
                dt.Rows.Add(gtri.MaDocGia, gtri.HoTenDocGia, gtri.NgaySinh, gtri.DiaChi, gtri.Email, gtri.NgayLapThe, gtri.TenLoaiDocGia, gtri.NgayHetHan, gtri.HoTenNhanVien, gtri.MaLoaiDocGia);
            }
            return dt;
        }

        // insert data
        public static void LapTheDG(DocGiaDTO the)
        {
            NorthwindDataContext QLTV = new NorthwindDataContext();
            DocGia d = new DocGia();
            d.HoTenDocGia = the.HoTenDocGia1;
            d.NgaySinh = the.NgaySinh1;
            d.DiaChi = the.DiaChi1;
            d.Email = the.Email1;
            d.NgayLapThe = the.NgayLapThe1;
            d.MaLoaiDocGia = the.MaLoaiDocGia1;
            d.MaNV = the.MaNV1;
            d.NgayHetHan = the.NgayHetHan1;
            QLTV.DocGias.InsertOnSubmit(d);
            QLTV.SubmitChanges();

        }

      // cap nhat 1 doc gia
        public static void  CapNhat_DocGia(DocGiaDTO dg)
        {
            NorthwindDataContext db = new NorthwindDataContext();
          
            var dGia = db.DocGias.Single(docGia => docGia.MaDocGia == dg.MaDocGia1);
            dGia.HoTenDocGia = dg.HoTenDocGia1;
            dGia.MaLoaiDocGia = dg.MaLoaiDocGia1;
            dGia.NgaySinh = dg.NgaySinh1;
            dGia.DiaChi = dg.DiaChi1;
            dGia.Email = dg.Email1;
            dGia.NgayLapThe = dg.NgayLapThe1;
            dGia.NgayHetHan = dg.NgayLapThe1.AddMonths(6);
            
           db.SubmitChanges();
        }

        
        //lay thong tin cua mot doc gia
        public static DocGia ThongTin_DocGia(int madg)
        {
            NorthwindDataContext db = new NorthwindDataContext();
          
   //         var dGia = db.DocGias.Single(docGia => docGia.MaDocGia == madg);
            var dGia = from dg in db.DocGias
                       where dg.MaDocGia == madg
                       select dg;
            DocGia docGia = new DocGia(); ;
            foreach (var gtri in dGia)
            {
                docGia.MaDocGia = gtri.MaDocGia;
                docGia.HoTenDocGia = gtri.HoTenDocGia;
                docGia.NgaySinh = gtri.NgaySinh;
                docGia.DiaChi = gtri.DiaChi;
                docGia.NgayHetHan = gtri.NgayHetHan;
                docGia.NgayLapThe = gtri.NgayLapThe;
                docGia.MaNV = gtri.MaNV;
            }
            return docGia;
        }

        public static string LayTenDocGia(int MaDG)
        {
            NorthwindDataContext cnn = new NorthwindDataContext();
            var sql = (from d in cnn.DocGias
                       where d.MaDocGia == MaDG
                       select d.HoTenDocGia).Max();
            return sql;
            
        }

        //lay ra tat ca cac cuon sach ma doc gia dang xet da muon
        //public static DataTable DS_SachDaMuon(int madg)
        //{
        //    NorthwindDataContext db = new NorthwindDataContext();
        //    var ds_SachDaMuon = from pm in db.PhieuMuons
        //             join ctpm in db.ChiTietPhieuMuons on pm.MaPhieuMuon equals ctpm.MaPhieuMuon
        //             where pm.MaDocGia == madg
        //             select new
        //             {
        //                 pm.NgayMuon,
        //                 ctpm.MaSach,
        //             };
        //    //lay ra ds cac cuon sach da tra
        //    var ds_DaTra

        //}
     
      
    }
}
